/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.CoursesBean;

/**
 *
 * @author Group 3 Aprotrain Aptech Group3 Aprotrain Aptech
 */
public class CoursesDAO {
    public static List<CoursesBean> getAllUsersByCondition() throws ClassNotFoundException, SQLException {
        List<CoursesBean> list = new ArrayList<CoursesBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblCourses");
        rs = pstm.executeQuery();

        CoursesBean courses = null;
        while (rs.next()) {
            courses = new CoursesBean();
            courses.setCoursesId(rs.getInt("coursesId"));
            courses.setCoursesName(rs.getString("coursesName"));
            courses.setDescription(rs.getString("description"));
            String attachment = rs.getString("attachment");
            String[] array = attachment.split("/");
            if(array.length <= 2){
                courses.setAttachment("");
            }
            else{
                courses.setAttachment(attachment);
            }
            courses.setDateStart(convertDateToString(rs.getDate("dateStart")));
            courses.setDateEnd(convertDateToString(rs.getDate("dateEnd")));
            list.add(courses);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static CoursesBean getCoursesById(int coursesId) throws ClassNotFoundException, SQLException {
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblCourses WHERE coursesId=?");
        pstm.setInt(1, coursesId);
        rs = pstm.executeQuery();

        CoursesBean courses = null;
        if (rs.next()) {
            courses = new CoursesBean();
            courses.setCoursesId(rs.getInt("coursesId"));
            courses.setCoursesName(rs.getString("coursesName"));
            courses.setDescription(rs.getString("description"));
            courses.setAttachment(rs.getString("attachment"));
            courses.setDateStart(convertDateToString(rs.getDate("dateStart")));
            courses.setDateEnd(convertDateToString(rs.getDate("dateEnd")));
        }
        else{
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return courses;
    }
    public static int updateCourses(CoursesBean courses) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [tblCourses] SET [coursesName]=?,[description]=?,[attachment]=?,[dateStart]=?,[dateEnd]=? WHERE [coursesId]=?");
        pstmt.setNString(1, courses.getCoursesName());
        pstmt.setNString(2, courses.getDescription());
        pstmt.setNString(3, courses.getAttachment());                       
        pstmt.setDate(4, getInsertDate(courses.getDateStart()));
        pstmt.setDate(5, getInsertDate(courses.getDateEnd()));
        pstmt.setInt(6, courses.getCoursesId());
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static int deleteCourses(int coursesId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM tblCourses WHERE coursesId=?");
        pstmt.setInt(1, coursesId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static int addCourses(CoursesBean courses) throws ClassNotFoundException, SQLException {
        int result = 0;
        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [tblCourses] ([coursesName],[description],[attachment],[dateStart],[dateEnd]) VALUES(?,?,?,?,?)");
        pstmt.setNString(1, courses.getCoursesName());
        pstmt.setNString(2, courses.getDescription());
        pstmt.setNString(3, courses.getAttachment());        
        pstmt.setDate(4, getInsertDate(courses.getDateStart()));
        pstmt.setDate(5, getInsertDate(courses.getDateEnd()));
        result = pstmt.executeUpdate();

        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static Date getInsertDate(String s){
        java.sql.Date dte=null;
        try{
        String str = s;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        java.util.Date dt = formatter.parse(str);
        dte=new java.sql.Date(dt.getTime());
        }catch(Exception e){
        e.printStackTrace();
        }
        return dte;
    }
    public static String convertDateToString(java.util.Date date) {
        SimpleDateFormat mySimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
        return mySimpleDateFormat.format(date);
    }

}
